package lms.dao.impl;

import lms.dao.BorrowDao;
import lms.entity.Book;
import lms.entity.Borrow;
import lms.entity.Reader;
import lms.utils.BookRowMapper;
import lms.utils.BorrowRowMapper;
import lms.utils.DBUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

/**
 * @description:
 * @author: Grape_Pip
 * @time: 2021/06/24
 */
public class BorrowDaoImpl implements BorrowDao {
    /**
     * 获取JDBCTemplate对象
     **/
    private JdbcTemplate template = new JdbcTemplate(DBUtils.getDataSource());
    /**
     * 数据库连接对象
     */
    Connection conn = null;
    /**
     * 数据库操作对象
     */
    PreparedStatement pstmt = null;
    /**
     * 结果集
     */
    ResultSet rs = null;

    @Override
    public int addBorrowInfo(Borrow bo) {
        try {
            String sql = "insert into l_borrow (bobISBN,borid,bosum,botime,bostatus,bfine) " +
                    "VALUES (?,?,?,?,?,?);";
            int res = template.update(sql, bo.getBorrowISBN(), bo.getBorrowReaderId(), bo.getBorrowSum(), bo.getBorrowDate(), bo.getBoStatus(), bo.getBorrowFine());
            return res;

        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

    }

    /**
     * @return int
     * @Description //TODO  查询书籍是否存在数据库
     * @Param [bId]
     **/
    public Book verifiedBook(Borrow bId) {
        try {
//            查询书籍是否存于数据库
            String sql = "select bISBN,bstocks from l_book where bISBN= ?";
            Book bookVerified = template.queryForObject(sql, new BeanPropertyRowMapper<Book>(Book.class), bId.getBorrowISBN());
            return bookVerified;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    /**
     * @return lms.entity.Reader
     * @Description //TODO  查询读者是否存在数据库
     * @Param [rId]
     **/
    public Reader verifyReader(Borrow rId) {
        try {
//            查询读者是否存于数据库
            String sql = "select rid, r.rtype, rtlimit,rtmaxnum from l_reader r,l_readertype rt " +
                    "where rid= ? and r.rtype=rt.rtype ";
            Reader readerVerified = template.queryForObject(sql, new BeanPropertyRowMapper<Reader>(Reader.class), rId.getBorrowReaderId());
            return readerVerified;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public int updateBook(Borrow bo) {
        try {
//            更新库存
            String sql = "UPDATE l_book set bstocks= ? " +
                    "WHERE  bISBN=? ";
            int res = template.update(sql, bo.getBorrowSum(), bo.getBorrowISBN());
            return res;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }


    @Override
    public int updateReturnInfo(int bid, int rid, Borrow bo) {
        try {
//            更新借阅表
            String sql = "UPDATE l_borrow set bosum=?,boreturntime=?,bostatus=?,bfine=? " +
                    "WHERE bobISBN=? " +
                    "AND borid=? " +
                    "AND bostatus=? ";
            int res = template.update(sql, bo.getBorrowSum(), bo.getReturnDate(), bo.getBoStatus(), bo.getBorrowFine(), bid, rid,0);
            return res;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public List<Borrow> selectBorrowInfo() {
        List<Borrow> borrowList;
        try {
            String sql = "select * from l_borrow where  bostatus=?";
            borrowList= template.query(sql,new BorrowRowMapper(),0);
            return borrowList;
        } catch (Exception e) {
//            e.printStackTrace();
            return null;
        }
    }

    @Override
    public Borrow selectBoByBidAndRid(int bid, int rid) {
        try {
            String sql = "select * from l_borrow where bobISBN = ? and borid= ? and bostatus=?";
            // 返回 Student对象
            return template.queryForObject(sql,new BorrowRowMapper(),bid,rid,0);
        } catch (Exception e) {
//            e.printStackTrace();
            return null;
        }
    }

    @Override
    public Borrow selectedBoByRid(int rid) {
        try {
            String sql = "select * from l_borrow where borid= ? and bostatus=?";
            // 返回 Student对象
            return template.queryForObject(sql,new BorrowRowMapper(),rid,0);
        } catch (Exception e) {
//            e.printStackTrace();
            return null;
        }
    }

    @Override
    public Borrow selectedBoByBid(int bid) {
        try {
            String sql = "select * from l_borrow where bobISBN= ? and bostatus=?";
            // 返回 Student对象
            return template.queryForObject(sql,new BorrowRowMapper(),bid,0);
        } catch (Exception e) {
//            e.printStackTrace();
            return null;
        }
    }
}
